import pandas, duckdb
from time import strftime
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# timenow = str(strftime("%Y%m%d%H%M%S"))
def run(dwbh, dwid, kmtx_id, zb, kjnd, filepath, timenow):
  excel_data = pandas.read_excel(filepath, sheet_name=None, dtype=str)
  df1 = excel_data.get('会计科目')
  conn = get_connection()
  conn.register('temp_kmzd_main', df1)

  conn.execute("""
               delete from T_API_KM where fiyear = ? and LEDGERBH = ?;
               """, [kjnd, zb])
  # conn.execute("""
  #       create or replace table t_yl_km as
  #              select
  #               uuid() as id,
  #               '"""+dwbh+"""' as 单位编号,
  #               '"""+zb+"""' as 账簿编号,
  #               '"""+kjnd+"""' as 会计年度,
  #               科目体系编号,
  #               科目体系名称,
  #               科目属性编号,
  #               科目属性名称,
  #               科目编号,
  #               科目名称,
  #               科目全称策略,
  #               科目全称,
  #               余额方向,
  #               银行账,
  #               是否启用,
  #               '"""+timenow+"""' as 更新时间
  #               from temp_kmzd_main;
  #   """)
#   print(zb)
  conn.execute("""
    insert into T_API_KM (ID, CHARTOFACCBH, CHARTOFACCNAME, ACCTITLEPROPBH, ACCTITLEPROP, CODE, NAME_CHS, FULLNAME_CHSCL, FULLNAME_CHS, BALANCEDIR, ISBANKBOOK, 
               ISDISABLED, esun_api_update, accorgid, accorgbh, fiyear, LEDGERBH, chartofacc)
              select
                uuid(),
                科目体系编号,
                科目体系名称,
                科目属性编号,
                科目属性名称,
                科目编号,
                科目名称,
                科目全称策略,
                科目全称,
                case 余额方向
                when '借' then '1'
                when '贷' then '2'
                end,
                银行账,
                case 是否启用
                when '启用' then '0'
                else '1'
                end,
                '"""+timenow+"""',
                '"""+dwid+"""',
                '"""+dwbh+"""',
                '"""+kjnd+"""',
                '"""+zb+"""',
                '"""+kmtx_id+"""'
               from temp_kmzd_main;
  """)

  conn.close()



# run('03047', '01051507', '2024', r'D:\code\app_report_back20241201\会计科目_数据导出.xlsx', timenow)